﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Sys_PersonnelManagement.DAL
{
    public class MSSqlServer
    {
        private SqlDataAdapter _adp;
        private SqlCommand _cmd;
        private SqlConnection _conn;
        private string _strConn;
        private string _strErr;

        public MSSqlServer()
        {
            this._strConn = @"Data Source=LAPTOP-FFNS7H5V\NEWSQL;Initial Catalog=PerManageInfo;Integrated Security=True";
        }
        public MSSqlServer(string strConn)
        {
            this._strConn = strConn;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseConn()
        {
            if (this._conn.State == ConnectionState.Open)
            {
                this._conn.Close();
                this._conn.Dispose();
                this._conn = null;
            }
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        public void OpenConn()
        {
            this._conn = new SqlConnection(this._strConn);
            if (this._conn.State == ConnectionState.Closed)
            {
                this._conn.Open();
            }
        }

        public string ConnectionString
        {
            get
            {
                return this._strConn;
            }
            set
            {
                this._strConn = value;
            }
        }
        /// <summary>
        /// 查询表格分页
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            DataTable result = null;
            try
            {
                this.OpenConn();
                this._cmd = new SqlCommand();
                this._cmd.Connection = this._conn;
                this._cmd.CommandText = cmdText;
                this._cmd.CommandType = cmdType;
                if (cmdParams != null)
                {
                    foreach (SqlParameter parm in cmdParams)
                    {
                        this._cmd.Parameters.Add(parm);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(this._cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (((ds != null) && (ds.Tables != null)) && (ds.Tables.Count > 0))
                {
                    result = ds.Tables[0];
                }
                this._cmd.Parameters.Clear();
                this.CloseConn();
            }
            catch (Exception)
            {
                this._conn = null;
            }
            return result;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public bool ExecuteEditSql(string Sql)
        {
            try
            {
                if (this._conn == null)
                {
                    this.OpenConn();
                }
                if (this._cmd == null)
                {
                    this._cmd = new SqlCommand();
                }
                this._cmd.Connection = this._conn;
                this._cmd.CommandText = Sql;
                _cmd.CommandTimeout = 500;
                this._cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                this._cmd = null;
                this._conn = null;
                return false;
            }
            finally
            {
                _cmd.Dispose();
                CloseConn();
            }
        }
        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public DataTable ExecuteSqlDataTable(string Sql)
        {
            //创建DataTable表类型变量result赋值为null    
            DataTable result = null;
            try
            {
                //调用打开数据库方法
                this.OpenConn();
                //创建适配器da，并设置sql和conn
                SqlDataAdapter da = new SqlDataAdapter(Sql, this._conn);
                //创建DataSet表类型ds，    DataSet是多张表的类型，每张表用ds.Tables[0],ds.Tables[1]来表示。 DataTable是只有一张表的类型
                DataSet ds = new DataSet();
                //把适配器中的数据给ds
                da.Fill(ds);
                //判断ds是否为空，是否有数据
                if (((ds != null) && (ds.Tables != null)) && (ds.Tables.Count > 0))
                {
                    //给DataTable类型的result赋值
                    result = ds.Tables[0];
                }
                //释放适配器里的资源
                da.Dispose();
                //关闭数据库链接
                this.CloseConn();
            }
            catch (Exception)
            {
                this._conn = null;
            }
            //返回
            return result;
        }
    }
}